package dao.implementations.postgresql.Gae;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.implementations.DBBase;
import dao.interfaces.Gae.IGae;
import dao.transferObjects.UsuarioTO;
import dao.transferObjects.Administrador.ProyectoTO;
import dao.transferObjects.Gae.CursoTO;
import dao.transferObjects.Gae.GaeTO;

public class GaeDB extends DBBase implements IGae {
	
	private static String CAMPOS_GAE =  " id_gae, rut_gae, nombre_gae, id_proyecto, id_curso, cantidad_alumnos, " +
										" nombre_representante, apellido_representante, tipo_fono, fono, " +
										" correo, nota1, nota2, nota3, nota_socio, nota_final, eliminado_gae, " +
										" matricula_representante ";
	
	public GaeDB(Connection connection){
		this.conn = connection;
	}
	
	public GaeTO resultSet_a_GaeTO(ResultSet rs)
	{
		GaeTO gae = new GaeTO();
		
		try{
			gae.setId_gae(rs.getInt("id_gae"));
			gae.setRut_gae(rs.getInt("rut_gae"));
			gae.setNombre_gae(rs.getString("nombre_gae"));
			gae.setId_proyecto(rs.getInt("id_proyecto"));
			gae.setId_curso(rs.getInt("id_curso"));
			gae.setCtd_alumno(rs.getInt("cantidad_alumnos"));
			gae.setNombreRepresentante(rs.getString("nombre_representante"));
			gae.setApellidoRepresentante(rs.getString("apellido_representante"));
			gae.setTipo_fono(rs.getString("tipo_fono"));
			gae.setFono(rs.getInt("fono"));
			gae.setCorreo(rs.getString("correo"));
			gae.setNota1(rs.getDouble("nota1"));
			gae.setNota2(rs.getDouble("nota2"));
			gae.setNota3(rs.getDouble("nota3"));
			gae.setNota_socio(rs.getDouble("nota_socio"));
			gae.setNota_final(rs.getDouble("nota_final"));
			gae.setEliminado(rs.getBoolean("eliminado_gae"));
			gae.setMatriculaRepresentante(rs.getInt("matricula_representante"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_CursoTO : " + e.getMessage());
		}
		
		return gae;
	}
	
	public int obtenerSgteIDGae()
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int resp = -1;
		String query = "SELECT nextval('gae_id_gae_seq') as NEXTVAL";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getInt("NEXTVAL");
			
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en GaeDB -> obtenerSgteIDGae() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}		
		return resp;
	}
	
	public int crearGAE(GaeTO gae) {
		
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO gae( "+
	            	   "id_gae, rut_gae, nombre_gae, id_curso, "+ 
	            	   "cantidad_alumnos, nombre_representante, apellido_representante, matricula_representante, tipo_fono, fono, correo, "+ 
	            	   "nota1, nota2, nota3, nota_socio, nota_final, eliminado_gae) "+
	            	   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, gae.getRut_gae());
			ps.setString(3, gae.getNombre_gae());
			//ps.setInt(4, gae.getId_proyecto());
			ps.setInt(4, gae.getId_curso());
			ps.setInt(5, gae.getCtd_alumno());
			ps.setString(6, gae.getNombreRepresentante());
			ps.setString(7, gae.getApellidoRepresentante());
			ps.setInt(8, gae.getMatriculaRepresentante());
			ps.setString(9, gae.getTipo_fono());
			ps.setInt(10, gae.getFono());
			ps.setString(11, gae.getCorreo());
			ps.setDouble(12, gae.getNota1());
			ps.setDouble(13, gae.getNota2());
			ps.setDouble(14, gae.getNota3());
			ps.setDouble(15, gae.getNota_socio());
			ps.setDouble(16, gae.getNota_final());
			ps.setBoolean(17, gae.getEliminado());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> crearGAE -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int modificarGae(GaeTO gaeNuevo){
		int result = -1;
		PreparedStatement pstmt = null;
		
		String query = " UPDATE gae "+
					   " SET nombre_gae=?, " +
					   " cantidad_alumnos=?, nombre_representante=?, apellido_representante=?, " +
					   " tipo_fono=?, fono=?, correo=?, matricula_representante=?"+
					   " WHERE id_gae=? AND rut_gae=?";
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setString(1, gaeNuevo.getNombre_gae());
			pstmt.setInt(2, gaeNuevo.getCtd_alumno());
			pstmt.setString(3, gaeNuevo.getNombreRepresentante());
			pstmt.setString(4, gaeNuevo.getApellidoRepresentante());
			pstmt.setString(5, gaeNuevo.getTipo_fono());
			pstmt.setInt(6, gaeNuevo.getFono());
			pstmt.setString(7, gaeNuevo.getCorreo());
			pstmt.setInt(8, gaeNuevo.getMatriculaRepresentante());
			pstmt.setInt(9, gaeNuevo.getId_gae());
			pstmt.setInt(10, gaeNuevo.getRut_gae());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> modificarGae -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	public int eliminarGae(GaeTO gae){
		int result = -1;
		PreparedStatement pstmt = null;
		
		String query = " DELETE FROM gae "+
					   " WHERE id_gae=? AND rut_gae=?";
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, gae.getId_gae());
			pstmt.setInt(2, gae.getRut_gae());			
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> eliminarGae -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	/*
	public int asignarAlumnoRepresentant(GaeTO gae){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=?, rut_gae=?, tipo_fono=?, fono=?, correo=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, gae.getRut_gae());
			ps.setString(3, gae.getTipo_fono());
			ps.setInt(4, gae.getFono());
			ps.setString(5, gae.getCorreo());
			ps.setInt(6, gae.getMatriculaRepresentante());
			ps.setInt(7, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> asignarAlumnoRepresentant -> "+ ex.getMessage());
		}		
		return result;
	}
	*/
	/*
	public int asignarAlumnoGae(GaeTO gae, AlumnoTO alumno){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, alumno.getMatricula());
			ps.setInt(3, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> asignarAlumnoGae -> "+ ex.getMessage());
		}		
		return result;
	}
	*/
	public List<GaeTO> obtenerGaesCurso(CursoTO curso)
	{
		List<GaeTO> listaGrupos = new ArrayList<GaeTO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+CAMPOS_GAE+
					   " FROM gae "+
					   " WHERE id_curso = ?" +
					   " ORDER BY nombre_gae ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaGrupos.add(this.resultSet_a_GaeTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerGaesCurso : " + sqle.getMessage());
		}
		
		return listaGrupos;
	}
	
	public List<GaeTO> obtenerGaesCursoNoAsociados(CursoTO curso)
	{
		List<GaeTO> listaGrupos = new ArrayList<GaeTO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+CAMPOS_GAE+
					   " FROM gae "+
					   " WHERE id_proyecto IS NULL AND id_curso=? " +
					   " ORDER BY nombre_gae ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaGrupos.add(this.resultSet_a_GaeTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerGaesCursoNoAsociados : " + sqle.getMessage());
		}
		
		return listaGrupos;
	}
	
	public List<GaeTO> obtenerGaesCursoAsociados(CursoTO curso)
	{
		List<GaeTO> listaGrupos = new ArrayList<GaeTO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+CAMPOS_GAE+
					   " FROM gae "+
					   " WHERE id_proyecto IS NOT NULL AND id_curso=? " +
					   " ORDER BY nombre_gae ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaGrupos.add(this.resultSet_a_GaeTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerGaesCursoAsociados : " + sqle.getMessage());
		}
		
		return listaGrupos;
	}
	
	public List<GaeTO> obtener_gaes_asociados_a_un_proyecto_X(ProyectoTO proyecto){
		List<GaeTO> listaGrupos = new ArrayList<GaeTO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+CAMPOS_GAE+
					   " FROM gae "+
					   " WHERE id_proyecto = ?" +
					   " ORDER BY nombre_gae ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, proyecto.getId_proyecto());
			rs = pstmt.executeQuery();
			
			while(rs.next()){				
				listaGrupos.add(this.resultSet_a_GaeTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtener_gaes_asociados_a_un_proyecto_X : " + sqle.getMessage());
		}
		
		return listaGrupos;
	}
	
	
	
	public int reAsignarRut_Gae(GaeTO gae, UsuarioTO usuario){
		int result = -1;
		PreparedStatement pstmt = null;
		
		String query = " UPDATE gae "+
					   " SET rut_gae=? "+
					   " WHERE id_gae=?";
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, usuario.getRut());
			pstmt.setInt(2, gae.getId_gae());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase GaeDB -> reAsignarRut_Gae -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	public int asignarProyectoGae(ProyectoTO proyecto, GaeTO gae){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE gae "+
		   			   " SET id_proyecto=? "+
				       " WHERE id_gae=? AND rut_gae=?; ";
		
		try{
			pstm = this.conn.prepareStatement(query);
			
			pstm.setInt(1, proyecto.getId_proyecto());
			pstm.setInt(2, gae.getId_gae());
			pstm.setInt(3, gae.getRut_gae());
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase GaeDB -> asignarProyectoGae -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int desAsignarProyectoGae(ProyectoTO proyecto, GaeTO gae){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE gae "+
		   			   " SET id_proyecto=? "+
				       " WHERE id_gae=? AND rut_gae=? AND id_proyecto=?; ";
		
		try{
			pstm = this.conn.prepareStatement(query);
			
			pstm.setNull(1, java.sql.Types.INTEGER);
			pstm.setInt(2, gae.getId_gae());
			pstm.setInt(3, gae.getRut_gae());
			pstm.setInt(4, proyecto.getId_proyecto());
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase GaeDB -> desAsignarProyectoGae -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public double obtenerPromedioGae(int id_gae, int rut_gae)
	{
		double resp = -1;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT ROUND ((nota1+nota2+nota3+nota_socio)/4,2) as promedio FROM gae WHERE id_gae = ? AND rut_gae = ? ";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			
			pstmt.setInt(1, id_gae);
			pstmt.setInt(2, rut_gae);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getDouble("promedio");
			
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en PautaEvaluacionDB -> obtenerPromedioGae() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return resp;
	}
	
	public int asignarNotaProducto(double nota, int id_gae, int rut_gae, int numero_producto){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query1 = " UPDATE gae "+
		   			   " SET nota1=? "+
				       " WHERE id_gae=? AND rut_gae=?; ";
		
		String query2 = " UPDATE gae "+
					   " SET nota2=? "+
				       " WHERE id_gae=? AND rut_gae=?; ";
		
		String query3 = " UPDATE gae "+
					   " SET nota3=? "+
				       " WHERE id_gae=? AND rut_gae=?; ";
		
		try{
			
			if(numero_producto==1)
				pstm = this.conn.prepareStatement(query1);
			else
				if(numero_producto==2)
					pstm = this.conn.prepareStatement(query2);
				else
					if(numero_producto==3)
						pstm = this.conn.prepareStatement(query3);			
			
			pstm.setDouble(1, nota);
			pstm.setInt(2, id_gae);
			pstm.setInt(3, rut_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase GaeDB -> asignarNotaProducto -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public int asignarNotaSocio(double promedioPautasGae, int id_gae, int rut_gae){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE gae "+
		   			   " SET nota_socio=? "+
				       " WHERE id_gae=? AND rut_gae=?; ";
		
		try{
			
			pstm = this.conn.prepareStatement(query);		
			
			pstm.setDouble(1, promedioPautasGae);
			pstm.setInt(2, id_gae);
			pstm.setInt(3, rut_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase GaeDB -> asignarNotaSocio -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public int asignarNotaFinal(int id_gae, int rut_gae){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE gae " +
					   " SET nota_final = (SELECT ROUND ((nota1+nota2+nota3+nota_socio)/4,2) as promedio FROM gae WHERE id_gae = ? AND rut_gae = ?) "+
		   			   " WHERE id_gae = ? AND rut_gae = ? ";
		
		try{
			
			pstm = this.conn.prepareStatement(query);		
			
			pstm.setInt(1, id_gae);
			pstm.setInt(2, rut_gae);
			pstm.setInt(3, id_gae);
			pstm.setInt(4, rut_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase GaeDB -> asignarNotaFinal -> "+ ex.getMessage());
		}		
		return result;
	}
}
